package com.databasteknik.krustykookiez;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class Database {
	public static final int COOKIES_ON_PALLET = 5400;
	public static final int AMOUNT_MULTIPLIER = 54;
	private final String[] searchIndex = {"barcodeNbr", "recipeName", "status", "customerName", "Pallets.orderId",  "productionDate", "deliveryDate" };
	private boolean flag;

	/**
	 * The database connection.
	 */
	private Connection conn;

	/**
	 * Create the database interface object. Connection to the database is
	 * performed later.
	 */
	public Database() {
		conn = null;
	}

	/**
	 * Open a connection to the database, using the specified user name and
	 * password.
	 * 
	 * @param userName
	 *            The user name.
	 * @param password
	 *            The user's password.
	 * @return true if the connection succeeded, false if the supplied user name
	 *         and password were not recognized. Returns false also if the JDBC
	 *         driver isn't found.
	 */
	public boolean openConnection(String userName, String password) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(
					"jdbc:mysql://puccini.cs.lth.se/" + userName, userName,
					password);
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	/**
	 * Close the connection to the database.
	 */
	public void closeConnection() {
		try {
			if (conn != null) {
				conn.close();
			}
		} catch (SQLException e) {
		}
		conn = null;
	}

	/**
	 * Check if the connection to the database has been established
	 * 
	 * @return true if the connection has been established
	 */
	public boolean isConnected() {
		return conn != null;
	}

	public ArrayList<String> fetchRecipes() {
		String s = "select * from Recipes";
		PreparedStatement st;
		ArrayList<String> ar = new ArrayList<String>();
		try {
			st = conn.prepareStatement(s);
			ResultSet set = st.executeQuery();
			while (set.next()) {
				ar.add(set.getString("name"));
			}
			return ar;

		} catch (SQLException e) {
			System.out.println("could not fetch recipes failed.");
			e.printStackTrace();
		}
		return null;
	}

	public boolean producePallets(String recipeName, int nbrOfPallets) {
		String s = "select ingredientName, amount from Contains where recipeName = ?";
		PreparedStatement st;
		try {
			st = conn.prepareStatement(s);
			st.setString(1, recipeName);
			ResultSet set = st.executeQuery();
			conn.setAutoCommit(false);
			while(set.next()) {
				s = "update Ingredients set amountInStorage = amountInStorage - ? where name = ?";
				st = conn.prepareStatement(s);
				//System.out.println("updating ingredient " + set.getString("IngredientName") + " with " + set.getString("amount"));
				st.setString(1, "" + (Double.parseDouble(set.getString("amount")) * AMOUNT_MULTIPLIER));
				st.setString(2, set.getString("IngredientName"));
				st.executeUpdate();
			}
			s = "select * from Ingredients where amountInStorage < 0";
			st = conn.prepareStatement(s);
			set = st.executeQuery();
			if(set.next()) {
				conn.rollback();
				conn.setAutoCommit(true);
				return false;
			} else {
				conn.commit();
				conn.setAutoCommit(true);
				s = "insert into Pallets(recipeName, status, productionDate) values(?, 'inStorage', ?)";
				st = conn.prepareStatement(s);
				st.setString(1, recipeName);
				st.setString(2, new Date(System.currentTimeMillis()).toString());
				for(int i = 0; i < nbrOfPallets; i++) {
					st.executeUpdate();
				}
				return true;
			}
		} catch (SQLException e) {
			System.out.println("something went wrong when producing " + nbrOfPallets + " of " + recipeName);
			e.printStackTrace();
			return false;
		}
	}
	public void setBlockedPallet(String palletID) {
		String input = "insert into BlockedPallets(barcodeNbr) values(?)";
		PreparedStatement st;
		setSuccessFlag(true);
		try {
			st = conn.prepareStatement(input);
			st.setString(1, palletID);
			st.executeUpdate();
		} catch (Exception e) {
			setSuccessFlag(false);
		}
	}
	
	public ArrayList<String[]> search(String[] values){
		ArrayList<String[]> result = new ArrayList<String[]>();
		if(!values[0].equals("")){
			try {
				String s = "select barcodeNbr, recipeName, status, customerName, Pallets.orderId,  productionDate, deliveryDate "
					 + "from Pallets left outer join Orders on Pallets.orderId = Orders.orderId "
					 + "where barcodeNbr = ?";			
				PreparedStatement st;
				st = conn.prepareStatement(s);
				st.setString(1, values[0]);
				ResultSet set = st.executeQuery();
				while(set.next()){
					String[] pallet = new String[searchIndex.length];
					for(int i = 0; i < pallet.length; i++){
						pallet[i] = set.getString(searchIndex[i]);
					}
					result.add(pallet);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}else{
			try {
				ArrayList<Integer> setValues = new ArrayList<Integer>();
				boolean first = true;
				String s = "select barcodeNbr, recipeName, status, customerName, Pallets.orderId,  productionDate, deliveryDate "
					 + "from Pallets left outer join Orders on Pallets.orderId = Orders.orderId";
				for(int i = 1; i < values.length-4; i++){
					if(!values[i].equals("")){
						setValues.add(new Integer(i));
						if(first){
							s += " where ";
							first = false;
						}else{
							s += " and ";
						}
						s += searchIndex[i] + " = ?";
					}
				}
				if(!values[values.length-4].equals("")){
					setValues.add(new Integer(values.length-4));
					if(first){
						s += " where ";
						first = false;
					}else{
						s += " and ";
					}
					s += searchIndex[5] + " > ?";
				}
				if(!values[values.length-3].equals("")){
					setValues.add(new Integer(values.length-3));
					if(first){
						s += " where ";
						first = false;
					}else{
						s += " and ";
					}
					s += searchIndex[5] + " < ?";
				}
				if(!values[values.length-2].equals("")){
					setValues.add(new Integer(values.length-2));
					if(first){
						s += " where ";
						first = false;
					}else{
						s += " and ";
					}
					s += searchIndex[6] + " > ?";
				}
				if(!values[values.length-1].equals("")){
					setValues.add(new Integer(values.length-1));
					if(first){
						s += " where ";
						first = false;
					}else{
						s += " and ";
					}
					s += searchIndex[6] + " < ?";
				}
				PreparedStatement st;
				st = conn.prepareStatement(s);
				for(int i = 0; i < setValues.size(); i++){
					st.setString(i+1, values[setValues.get(i)]);	
				}
				ResultSet set = st.executeQuery();
				while(set.next()){
					String[] pallet = new String[searchIndex.length];
					for(int i = 0; i < searchIndex.length; i++){
						pallet[i] = set.getString(searchIndex[i]);
					}
					result.add(pallet);
				}
			} catch (SQLException e){
				e.printStackTrace();
			}
		}
		
		return result;
	}
	
	public void setSuccessFlag(boolean b){
		flag = b;
	}
	public boolean getSuccessFlag(){
		return flag;
	}
	
	public ArrayList<String[]> getBlocked() {
		String s = "select barcodeNbr, recipeName, productionDate from blockedPallets natural join Pallets";
		PreparedStatement st;
		ArrayList<String[]> ar = new ArrayList<String[]>();
		try {
			st = conn.prepareStatement(s);
			ResultSet set = st.executeQuery();
			while (set.next()) {
				ar.add(new String[]{set.getString("barcodeNbr"), set.getString("recipeName"), set.getString("productionDate")});
			}
			return ar;

		} catch (SQLException e) {
			System.out.println("Could not fetch blocked pallets.");
			e.printStackTrace();
		}
		return null;
		
		
	}
	
	
}

